/*
THIS FILE CONVERTS THE EQUITY PRICE INDEX DOWNLOADED FROM DATASTREAM IN A .DTA FORMAT.
IN THE EXCEL FILE OBTAINED FROM DATASTREAM, EACH SHEET CORRESPONDS TO A COUNTRY. IMPORT EACH SHEET SEPARETELY AND MERGE THEM.
*/
clear all
global countries "  australia    austria    belgium    canada    brazil    chile    china    colombia    czechrepublic    denmark    egypt    france    finland    germany    greece    hongkong    hungary    india    indonesia    ireland    israel    italy    japan    netherlands    newzealand    panama    kuwait    norway    portugal    singapore    spain    sweden    switzerland    argentina    bahrain    bangladesh    bosniaherzegovina    botswana    bulgaria  cyprus  croatia    ecuador    estonia    ghana    iceland    ivorycoast    jamaica    kazakhstan    kenya    latvia    jordan    lebanon    lithuania    mauritius    morocco    namibia    nigeria    oman    pakistan    romania    saudiarabia    serbia    slovakia    slovenia    srilanka    trinidadandtobago    tunisia    ukraine    vietnam    zambia    zimbabwe    unitedkingdom    koreasouth    malaysia    mexico    peru    philippines    poland    russianfederation    qatar    southafrica    taiwan    thailand    turkey    unitedarabemirates    unitedstates            "
global nonUScountries "  australia    austria    belgium    canada    brazil    chile    china    colombia    czechrepublic    denmark    egypt    france    finland    germany    greece    hongkong    hungary    india    indonesia    ireland    israel    italy    japan    netherlands    newzealand    panama    kuwait    norway    portugal    singapore    spain    sweden    switzerland    argentina    bahrain    bangladesh    bosniaherzegovina    botswana    bulgaria cyprus   croatia    ecuador    estonia    ghana    iceland    ivorycoast    jamaica    kazakhstan    kenya    latvia    jordan    lebanon    lithuania    mauritius    morocco    namibia    nigeria    oman    pakistan    romania    saudiarabia    serbia    slovakia    slovenia    srilanka    trinidadandtobago    tunisia    ukraine    vietnam    zambia    zimbabwe    unitedkingdom    koreasouth    malaysia    mexico    peru    philippines    poland    russianfederation    qatar    southafrica    taiwan    thailand    turkey    unitedarabemirates               "


* Import excel files containing the msci index, save it locally, merge them together and reshape them in a nice panel form
foreach sheet of global countries{
	display "`sheet'"
	tempfile tempeq_`sheet'
	qui import excel "$path/datastream/daily_equity_indices_clean.xlsx", clear sheet("`sheet'") cellrange(A6) firstrow 
	ds, has(type string) 
	qui foreach v in `r(varlist)' { 
		replace `v' = "" if trim(`v') == "NA" 
	} 
	qui destring _all, replace force
	qui gen datem = mofd(date)
	format datem %tm
	rename date dated
	format dated %td
	*qui drop date
	qui drop if missing(datem)
	
	qui save "`tempeq_`sheet''", replace
}

* merge
use "`tempeq_unitedstates'", clear
foreach sheet of global nonUScountries {
		merge 1:1 dated using "`tempeq_`sheet''", nogenerate
}
// Delete each variable without any observation for all countries
foreach var of varlist _all {
	capture assert mi(`var')
	 if !_rc {
		drop `var'
	 }
}
	
* reshape
qui reshape long msci_ usdmsci_ , i(dated) j(country) string
/*
- msci: equity price index in local currency
- usdmsci: equity price index in USD
*/

* rename
foreach var in "usdmsci" "msci"  { 
	qui rename `var'_ `var'
}

* drop countries without any observation
bys country (datem) : egen meanusdreturn = mean(usdmsci)
drop if mi(meanusdreturn)
drop mean*

* save
save "$datapath/MSCI_daily_exceldata_to_DTA.dta", replace
* end
*************************************************************